IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID (N'[dbo].[sp_Report_HangTonTheoTungNhomHang]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[sp_Report_HangTonTheoTungNhomHang]
GO


CREATE PROC [sp_Report_HangTonTheoTungNhomHang]
(
	@Ma_chi_nhanh		INT,
	@Ma_nhom_hang		INT,
	@Tu_ngay			DATETIME,
	@Den_ngay			DATETIME	
)
AS
BEGIN
	DECLARE @Tu_ngay_convert	NVARCHAR(10)
	DECLARE @Den_ngay_convert	NVARCHAR(10)

	SET @Tu_ngay_convert = CONVERT(NVARCHAR, DAY(@Tu_ngay)) + '/' + CONVERT(NVARCHAR, MONTH(@Tu_ngay)) + '/' + CONVERT(NVARCHAR, YEAR(@Tu_ngay))
	SET @Den_ngay_convert = CONVERT(NVARCHAR, DAY(@Den_ngay)) + '/' + CONVERT(NVARCHAR, MONTH(@Den_ngay)) + '/' + CONVERT(NVARCHAR, YEAR(@Den_ngay))

	SELECT A.Ten AS 'Ten_nhom_hang',
		B.Ma_hang AS 'Ma_hang_hoa',
		B.Ten AS 'Ten_hang_hoa',
		F.Don_vi_goc AS 'Don_vi_tinh',
		SUM(CASE 
				WHEN G.Loai_nhap_xuat = 0 AND CONVERT(NVARCHAR,G.Ngay_cap_nhat_cuoi,111) BETWEEN @Tu_ngay AND @Den_ngay 
				THEN E.So_luong 
				ELSE 0 END) AS 'SL_xuat',
		SUM(CASE 
				WHEN G.Loai_nhap_xuat = 4 AND CONVERT(NVARCHAR,G.Ngay_cap_nhat_cuoi,111) BETWEEN @Tu_ngay AND @Den_ngay  
				THEN E.So_luong 
				ELSE 0 END) AS 'SL_nhap',
		SUM(CASE 
				WHEN G.Loai_nhap_xuat = 0 AND CONVERT(NVARCHAR,G.Ngay_cap_nhat_cuoi,111) > @Den_ngay AND CONVERT(NVARCHAR,G.Ngay_cap_nhat_cuoi,111) <= CONVERT(NVARCHAR,C.Ngay_cap_nhat_cuoi,111) 
				THEN E.So_luong 
				ELSE 0 END) AS 'SL_xuat_den_hien_tai',
		SUM(CASE 
				WHEN G.Loai_nhap_xuat = 4 AND CONVERT(NVARCHAR,G.Ngay_cap_nhat_cuoi,111) > @Den_ngay AND CONVERT(NVARCHAR,G.Ngay_cap_nhat_cuoi,111) <= CONVERT(NVARCHAR,C.Ngay_cap_nhat_cuoi,111)  
				THEN E.So_luong 
				ELSE 0 END) AS 'SL_nhap_den_hien_tai',
		C.So_luong AS 'SL_hang_ton',
		C.Gia_nhap_cuoi AS 'Gia_nhap',
		@Tu_ngay_convert AS 'Tu_ngay',
		@Den_ngay_convert AS 'Den_ngay'
	FROM NhomHang AS A INNER JOIN HangHoa AS B ON A.id = B.Ma_nhom_hang 
		INNER JOIN HangTon AS C ON B.id = C.Ma_hang 
		INNER JOIN KhoHang AS D ON C.Ma_kho_hang = D.id 
		INNER JOIN ChiTietNhapXuat AS E ON B.id = E.Ma_hang_hoa 
		INNER JOIN DonViQuiDoi AS F ON B.Ma_don_vi_qui_doi = F.id
		INNER JOIN PhieuNhapXuat AS G ON G.id = E.Ma_phieu_nhap_xuat
	WHERE D.Ma_chi_nhanh = @Ma_chi_nhanh
		AND A.id = @Ma_nhom_hang
	GROUP BY A.Ten, B.Ma_hang, B.Ten, F.Don_vi_goc, C.So_luong, C.Gia_nhap_cuoi
END


